/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package test_app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author assa
 */
public class Sql_helper {
    
        private Connection con = null;
        private Statement st = null; //Можно сделать локальной но в stop не потушить
        private PreparedStatement pst = null; //Можно сделать локальной
        private ResultSet rs = null;
        private String url = "jdbc:postgresql://ec2-107-21-235-125.compute-1.amazonaws.com/postgres";
        private String user = "postgres";
        private String password = "postgres";
        
    public Sql_helper(){
        create_db();
    }//Constructor

    public Sql_helper(String url, String user, String password ){
        this.url = url;
        this.user = user;
        this.password = password;
        create_db();
    }//Constructor

    
    private void create_db(){
         try {
            String stm = "CREATE TABLE IF NOT EXISTS agency ("
                         +"id serial PRIMARY KEY,"
                         +"data TEXT);";

            con = DriverManager.getConnection(url, user, password);
            pst = con.prepareStatement(stm);
            pst.executeUpdate();
            //st = con.createStatement();
            //st.executeUpdate(stm);
//            if (rs.next()) {
//                System.out.println(rs.getString(1));
//            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Sql_helper.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
            stop();
        }
    }
    
    
    private void stop(){
             try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (st != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Sql_helper.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
    }//stop
    
    public void write_to_db(String data){
           System.out.println("Write_to_db");

    String stm = "INSERT INTO agency(data) VALUES(?)";
        try {
            pst = con.prepareStatement(stm);
            pst.setString(1, data);                    
            pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Sql_helper.class.getName()).log(Level.SEVERE, null, ex);
            stop();
        }
    }//write_to_db
}
